import logging
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import openpyxl
import datetime
import os
import sys

from openpyxl.cell import MergedCell

# dirPath = os.path.dirname(os.path.realpath(sys.executable))
timeCur = datetime.datetime.now()
dirPath = 'E:\\日常工作\\11月\\6\\每日必办'
logFile = dirPath + 'AutoImportExcel.log'
# 创建logger对象
logger = logging.getLogger('test_logger')
# 设置日志等级
logger.setLevel(logging.DEBUG)
# 追加写入文件a ，设置utf-8编码防止中文写入乱码
test_log = logging.FileHandler('test.log', 'a', encoding='utf-8')
# 向文件输出的日志级别
test_log.setLevel(logging.DEBUG)
# 向文件输出的日志信息格式
formatter = logging.Formatter('%(asctime)s - %(filename)s - line:%(lineno)d - %(levelname)s - %(message)s -%(process)s')
test_log.setFormatter(formatter)
# 加载文件到logger对象中
logger.addHandler(test_log)
log = logging.getLogger()
stdout_handler = logging.StreamHandler(sys.stdout)
log.addHandler(stdout_handler)

fileJieDan = dirPath + '\\本考期案件明细表-总量.xlsx'
fileKaoHe = dirPath + '\\本考期案件明细表-考核.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'
fileKaoHePiPei = dirPath + '\\本考期案件明细表-考核-已匹配承办部门.xlsx'
fileJieDanPiPei = dirPath + '\\本考期案件明细表-总量-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\本考期案件明细表-总量-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultSave = dirPath + '\\考核情况得分统计表.xlsx'

bookKaoHe = openpyxl.load_workbook(fileKaoHe)
sheetKaoHe = bookKaoHe.active
maxRowZongLiang = sheetKaoHe.max_row
maxColZongLiang = sheetKaoHe.max_column
logger.debug('本考期案件明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowZongLiang, maxColZongLiang))


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, index_col, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(index_col, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource


# 合并 固定的列
def he_bing_cols(sheet_source, index_title, col_name_1, col_name_2, clo_name_out):
    maxRowSource = sheet_source.max_row
    colIndex1 = get_col_index(sheet_source, index_title, col_name_1)
    colIndex2 = get_col_index(sheet_source, index_title, col_name_2)
    colIndexOut = get_col_index(sheet_source, index_title, clo_name_out)
    for colHeBing in range(index_title+1, maxRowSource+1):
        valueColOne = sheet_source.cell(colHeBing, colIndex1).value
        valueColTwo = sheet_source.cell(colHeBing, colIndex2).value
        if valueColOne is not None:
            valueColOne = valueColOne.strip()
            if valueColTwo is not None:
                valueColTwo = valueColTwo.strip()
                cellValueOut = valueColOne + '\n' + valueColTwo
                sheet_source.cell(colHeBing, colIndexOut).value = cellValueOut


bookQuanBu = openpyxl.load_workbook(fileQuanBu)
sheetQuanBu = bookQuanBu.active
maxRowQuanBu = sheetQuanBu.max_row
maxColQuanBu = sheetQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowQuanBu, maxColQuanBu))


def pi_pei_cheng_ban_jie_dan(sheet_src, sheet_quan):
    maxRowSource = sheet_src.max_row
    colGongDanSrc = get_col_index(sheet_src, 2, '工单编号')
    colChengBanSrc = get_col_index(sheet_src, 2, '承办部门')
    colJieDanSrc = get_col_index(sheet_src, 2, '接单部门')
    maxRowQuanBuCur = sheet_quan.max_row
    colGongDanQuan = get_col_index(sheet_quan, 1, '工单编号')
    colChengBanQuan = get_col_index(sheet_quan, 1, '承办部门')
    colJieDanQuan = get_col_index(sheet_quan, 1, '接单部门')
    for rowSrc in range(3, maxRowSource+1):
        valueGongDanSrc = sheet_src.cell(rowSrc, colGongDanSrc).value
        if valueGongDanSrc is not None:
            valueGongDanSrc = valueGongDanSrc.strip()
            for rowQuan in range(2, maxRowQuanBuCur+1):
                valueGongDan = sheet_quan.cell(rowQuan, colGongDanQuan).value
                if valueGongDan is not None:
                    if valueGongDan.find(valueGongDanSrc) != -1:
                        strChengBan = sheet_quan.cell(rowQuan, colChengBanQuan).value
                        strJieDan = sheet_quan.cell(rowQuan, colJieDanQuan).value
                        sheet_src.cell(rowSrc, colChengBanSrc).value = strChengBan
                        sheet_src.cell(rowSrc, colJieDanSrc).value = strJieDan
                        break


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, 2, col_title)
    maxRowSource = sheet_source.max_row
    for rowSource in range(3, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            isFindSource = cellValueSearch.find(col_source)
            if isFindSource != -1:
                sheet_source.cell(rowSource, colTitleIndex).value = col_dest

# 合并来电人和联系方式
logger.debug('合并来电人和联系方式')
colLaiDianRen = get_col_index(sheetKaoHe, 2, '来电人')
sheetKaoHe.insert_cols(colLaiDianRen, 1)
sheetKaoHe.cell(2, colLaiDianRen).value = '来电人（合并）'
he_bing_cols(sheetKaoHe, 2, '来电人', '来电号码', '来电人（合并）')

colChengBan = get_col_index(sheetKaoHe, 2, '承办单位')
sheetKaoHe.insert_cols(colChengBan, 2)
sheetKaoHe.cell(2, colChengBan).value = '承办部门'
sheetKaoHe.cell(2, colChengBan+1).value = '接单部门'

pi_pei_cheng_ban_jie_dan(sheetKaoHe, sheetQuanBu)
# 修改特定的承办部门
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难工单', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难案件', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '综合城管', '综合行政执法队')

# 保存匹配承办部门后的表格
bookKaoHe.save(fileKaoHePiPei)
logger.debug('保存  {}'.format(fileKaoHePiPei))

# 打开 本考期案件明细表-总量.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDan)
sheetJieDan = bookJieDan.active
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('本考期案件明细表-总量.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

colChengBanJieDan = get_col_index(sheetJieDan, 2, '承办单位')
sheetJieDan.insert_cols(colChengBanJieDan, 2)
sheetJieDan.cell(2, colChengBanJieDan).value = '承办部门'
sheetJieDan.cell(2, colChengBanJieDan+1).value = '接单部门'

pi_pei_cheng_ban_jie_dan(sheetJieDan, sheetQuanBu)
# 修改特定的承办部门
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难工单', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难案件', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '综合城管', '综合行政执法队')

# 保存匹配承办部门后的表格
bookJieDan.save(fileJieDanPiPei)
logger.debug('保存  {}'.format(fileJieDanPiPei))


def init_excel_title(sheet_source, str_title):
    sheet_source.merge_cells(range_string='A1:AI1')
    sheet_source.cell(1, 1).value = str_title
    align = Alignment(horizontal='center', vertical='center', wrapText=True)
    sheet_source.cell(1, 1).alignment = align
    # 设置行高 第一行 40
    sheet_source.row_dimensions[1].height = 40

    # 序号
    sheet_source.merge_cells(range_string='A2:A3')
    sheet_source.cell(2, 1).value = '序号'
    sheet_source.cell(2, 1).alignment = align
    sheet_source.column_dimensions[get_column_letter(1)].width = 5
    # 接单部门
    sheet_source.merge_cells(range_string='B2:B3')
    sheet_source.cell(2, 2).value = '接单部门'
    sheet_source.cell(2, 2).alignment = align
    sheet_source.column_dimensions[get_column_letter(2)].width = 18
    # 接单总量
    sheet_source.merge_cells(range_string='C2:E2')
    sheet_source.cell(2, 3).value = '接单总量(件)'
    sheet_source.cell(2, 3).alignment = align
    # 纳入考核
    sheet_source.merge_cells(range_string='F2:H2')
    sheet_source.cell(2, 6).value = '纳入考核(件)'
    sheet_source.cell(2, 6).alignment = align
    # 联系(件)
    sheet_source.merge_cells(range_string='I2:J2')
    sheet_source.cell(2, 9).value = '联系(件)'
    sheet_source.cell(2, 9).alignment = align
    # 未联系(件)
    sheet_source.merge_cells(range_string='K2:L2')
    sheet_source.cell(2, 11).value = '未联系(件)'
    sheet_source.cell(2, 11).alignment = align
    # 解决(件)
    sheet_source.merge_cells(range_string='M2:N2')
    sheet_source.cell(2, 13).value = '解决(件)'
    sheet_source.cell(2, 13).alignment = align
    # 未解决(件)
    sheet_source.merge_cells(range_string='O2:P2')
    sheet_source.cell(2, 15).value = '未解决(件)'
    sheet_source.cell(2, 15).alignment = align
    # 满意(件)
    sheet_source.merge_cells(range_string='Q2:R2')
    sheet_source.cell(2, 17).value = '满意(件)'
    sheet_source.cell(2, 17).alignment = align
    # 基本满意(件)
    sheet_source.merge_cells(range_string='S2:T2')
    sheet_source.cell(2, 19).value = '基本满意(件)'
    sheet_source.cell(2, 19).alignment = align
    # 不满意(件)
    sheet_source.merge_cells(range_string='U2:V2')
    sheet_source.cell(2, 21).value = '不满意(件)'
    sheet_source.cell(2, 21).alignment = align
    # 响应率（%）
    sheet_source.merge_cells(range_string='W2:Y2')
    sheet_source.cell(2, 23).value = '响应率（%）'
    sheet_source.cell(2, 23).alignment = align
    # 解决率（%）
    sheet_source.merge_cells(range_string='Z2:AB2')
    sheet_source.cell(2, 26).value = '解决率（%）'
    sheet_source.cell(2, 26).alignment = align
    # 满意率（%）
    sheet_source.merge_cells(range_string='AC2:AE2')
    sheet_source.cell(2, 29).value = '满意率（%）'
    sheet_source.cell(2, 29).alignment = align
    # 得分
    sheet_source.merge_cells(range_string='AF2:AF3')
    sheet_source.cell(2, 32).value = '得分'
    sheet_source.cell(2, 32).alignment = align
    # 裸分得分
    sheet_source.merge_cells(range_string='AG2:AG3')
    sheet_source.cell(2, 33).value = '裸分得分'
    sheet_source.cell(2, 33).alignment = align
    sheet_source.column_dimensions[get_column_letter(33)].width = 5
    # 裸分失分
    sheet_source.merge_cells(range_string='AH2:AH3')
    sheet_source.cell(2, 34).value = '裸分失分'
    sheet_source.cell(2, 34).alignment = align
    sheet_source.column_dimensions[get_column_letter(34)].width = 5
    # 排名
    sheet_source.merge_cells(range_string='AI2:AI3')
    sheet_source.cell(2, 35).value = '排名'
    sheet_source.cell(2, 35).alignment = align
    sheet_source.column_dimensions[get_column_letter(35)].width = 5

    for rowTitle in range(3, 9):
        sheet_source.column_dimensions[get_column_letter(rowTitle)].width = 6
        if rowTitle % 3 == 0:
            sheet_source.cell(3, rowTitle).value = '总量'
            sheet_source.cell(3, rowTitle).alignment = align
        if rowTitle % 3 == 1:
            sheet_source.cell(3, rowTitle).value = '转派'
            sheet_source.cell(3, rowTitle).alignment = align
        if rowTitle % 3 == 2:
            sheet_source.cell(3, rowTitle).value = '直派'
            sheet_source.cell(3, rowTitle).alignment = align

    for rowTitle in range(9, 23):
        sheet_source.column_dimensions[get_column_letter(rowTitle)].width = 6
        if rowTitle % 2 == 0:
            sheet_source.cell(3, rowTitle).value = '直派'
            sheet_source.cell(3, rowTitle).alignment = align
        else:
            sheet_source.cell(3, rowTitle).value = '转派'
            sheet_source.cell(3, rowTitle).alignment = align

    for rowTitle in range(23, 32):
        sheet_source.column_dimensions[get_column_letter(rowTitle)].width = 6
        if rowTitle % 3 == 0:
            sheet_source.cell(3, rowTitle).value = '直派'
            sheet_source.cell(3, rowTitle).alignment = align
        if rowTitle % 3 == 1:
            sheet_source.cell(3, rowTitle).value = '平均'
            sheet_source.cell(3, rowTitle).alignment = align
        if rowTitle % 3 == 2:
            sheet_source.cell(3, rowTitle).value = '转派'
            sheet_source.cell(3, rowTitle).alignment = align


# '拱辰街道科室诉求总量反映情况统计（2022年10月19日0时至10月31日9时）'
strTitleKeShi = '拱辰街道科室诉求总量反映情况统计\r\n（'
if timeCur.day <= 19:
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month-1)
else:
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
strTitleKeShi = strTitleKeShi + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
sheetKeShi = bookKaoHe.create_sheet(title='科室', index=1)
init_excel_title(sheetKeShi, strTitleKeShi)


# 拱辰街道社区、村诉求考核反映情况统计（2022年9月19日0时至10月8日12时）
strTitleSheQu = '拱辰街道社区、村诉求总量反映情况统计\r\n（'
if timeCur.day <= 19:
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month-1)
else:
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
strTitleSheQu = strTitleSheQu + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
sheetSheQu = bookKaoHe.create_sheet(title='社区、村', index=1)
init_excel_title(sheetSheQu, strTitleSheQu)

bookChengBabBuMen = openpyxl.load_workbook(fileChengBanBuMen)
sheetChengBabBuMen = bookChengBabBuMen['承办部门']
maxRowChengBabBuMen = sheetChengBabBuMen.max_row
maxColChengBabBuMen = sheetChengBabBuMen.max_column
logger.debug('承办部门.xlsx-------行数：{}行    列数：{}列'.format(maxRowChengBabBuMen, maxColChengBabBuMen))
listKeShi = []
listSheQu = []
listKaoHeZhuan = {}
listKaoHeZhi = {}
listLianXiZhuan = {}
listLianXiZhi = {}
listWeiLianXiZhuan = {}
listWeiLianXiZhi = {}
listJieJueZhuan = {}
listJieJueZhi = {}
listWeiJieJueZhuan = {}
listWeiJieJueZhi = {}
listManYiZhuan = {}
listManYiZhi = {}
listJiBenZhuan = {}
listJiBenZhi = {}
listBuManYiZhuan = {}
listBuManYiZhi = {}

lvLianXiZhi = {}
lvWeiLianXiZhi = {}
lvJieJueZhi = {}
lvWeiJieJueZhi = {}
lvManYiZhi = {}
lvJiBenZhi = {}
lvBuManYiZhi = {}
lvManYiFinalZhi = {}
lvLianXiZhuan = {}
lvWeiLianXiZhuan = {}
lvJieJueZhuan = {}
lvWeiJieJueZhuan = {}
lvManYiZhuan = {}
lvJiBenZhuan = {}
lvBuManYiZhuan = {}
lvManYiFinalZhuan = {}

lvLianXiAverage = {}
lvWeiLianXiAverage = {}
lvJieJueAverage = {}
lvWeiJieJueAverage = {}
lvManYiAverage = {}
lvJiBenAverage = {}
lvBuManYiAverage = {}
lvManYiFinalAverage = {}

listJieDanZhi = {}
listJieDanZhuan = {}

deFenLuoFen = {}
shiFenLuoFen = {}

logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))

for rowKeshiList in range(3, maxRowChengBabBuMen+1):
    valueCellKeShi = sheetChengBabBuMen.cell(rowKeshiList, 1).value
    if valueCellKeShi is not None:
        listKeShi.append(valueCellKeShi)
        listKaoHeZhuan[valueCellKeShi] = 0
        listKaoHeZhi[valueCellKeShi] = 0
        listLianXiZhuan[valueCellKeShi] = 0
        listLianXiZhi[valueCellKeShi] = 0
        listJieJueZhuan[valueCellKeShi] = 0
        listJieJueZhi[valueCellKeShi] = 0
        listManYiZhuan[valueCellKeShi] = 0
        listManYiZhi[valueCellKeShi] = 0
        listJiBenZhuan[valueCellKeShi] = 0
        listJiBenZhi[valueCellKeShi] = 0
        listWeiLianXiZhuan[valueCellKeShi] = 0
        listWeiLianXiZhi[valueCellKeShi] = 0
        listWeiJieJueZhuan[valueCellKeShi] = 0
        listWeiJieJueZhi[valueCellKeShi] = 0
        listBuManYiZhuan[valueCellKeShi] = 0
        listBuManYiZhi[valueCellKeShi] = 0
        listJieDanZhi[valueCellKeShi] = 0
        listJieDanZhuan[valueCellKeShi] = 0
        lvLianXiZhi[valueCellKeShi] = None
        lvWeiLianXiZhi[valueCellKeShi] = None
        lvJieJueZhi[valueCellKeShi] = None
        lvWeiJieJueZhi[valueCellKeShi] = None
        lvManYiZhi[valueCellKeShi] = None
        lvJiBenZhi[valueCellKeShi] = None
        lvBuManYiZhi[valueCellKeShi] = None
        lvManYiFinalZhi[valueCellKeShi] = None
        lvManYiFinalZhi[valueCellKeShi] = None
        lvLianXiZhuan[valueCellKeShi] = None
        lvWeiLianXiZhuan[valueCellKeShi] = None
        lvJieJueZhuan[valueCellKeShi] = None
        lvWeiJieJueZhuan[valueCellKeShi] = None
        lvManYiZhuan[valueCellKeShi] = None
        lvJiBenZhuan[valueCellKeShi] = None
        lvBuManYiZhuan[valueCellKeShi] = None
        lvManYiFinalZhuan[valueCellKeShi] = None
        lvLianXiAverage[valueCellKeShi] = None
        lvWeiLianXiAverage[valueCellKeShi] = None
        lvJieJueAverage[valueCellKeShi] = None
        lvWeiJieJueAverage[valueCellKeShi] = None
        lvManYiAverage[valueCellKeShi] = None
        lvJiBenAverage[valueCellKeShi] = None
        lvBuManYiAverage[valueCellKeShi] = None
        lvManYiFinalAverage[valueCellKeShi] = None
        deFenLuoFen[valueCellKeShi] = None
        shiFenLuoFen[valueCellKeShi] = None

for rowSheQuList in range(3, maxRowChengBabBuMen+1):
    valueCellSheQu = sheetChengBabBuMen.cell(rowSheQuList, 2).value
    if valueCellSheQu is not None:
        listSheQu.append(valueCellSheQu)
        listKaoHeZhuan[valueCellSheQu] = 0
        listKaoHeZhi[valueCellSheQu] = 0
        listLianXiZhuan[valueCellSheQu] = 0
        listLianXiZhi[valueCellSheQu] = 0
        listJieJueZhuan[valueCellSheQu] = 0
        listJieJueZhi[valueCellSheQu] = 0
        listManYiZhuan[valueCellSheQu] = 0
        listManYiZhi[valueCellSheQu] = 0
        listJiBenZhuan[valueCellSheQu] = 0
        listJiBenZhi[valueCellSheQu] = 0
        listWeiLianXiZhuan[valueCellSheQu] = 0
        listWeiLianXiZhi[valueCellSheQu] = 0
        listWeiJieJueZhuan[valueCellSheQu] = 0
        listWeiJieJueZhi[valueCellSheQu] = 0
        listBuManYiZhuan[valueCellSheQu] = 0
        listBuManYiZhi[valueCellSheQu] = 0
        listJieDanZhi[valueCellSheQu] = 0
        listJieDanZhuan[valueCellSheQu] = 0
        lvLianXiZhi[valueCellSheQu] = None
        lvWeiLianXiZhi[valueCellSheQu] = None
        lvJieJueZhi[valueCellSheQu] = None
        lvWeiJieJueZhi[valueCellSheQu] = None
        lvManYiZhi[valueCellSheQu] = None
        lvJiBenZhi[valueCellSheQu] = None
        lvBuManYiZhi[valueCellSheQu] = None
        lvManYiFinalZhi[valueCellSheQu] = None
        lvLianXiZhuan[valueCellSheQu] = None
        lvWeiLianXiZhuan[valueCellSheQu] = None
        lvJieJueZhuan[valueCellSheQu] = None
        lvWeiJieJueZhuan[valueCellSheQu] = None
        lvManYiZhuan[valueCellSheQu] = None
        lvJiBenZhuan[valueCellSheQu] = None
        lvBuManYiZhuan[valueCellSheQu] = None
        lvManYiFinalZhuan[valueCellSheQu] = None
        lvLianXiAverage[valueCellSheQu] = None
        lvWeiLianXiAverage[valueCellSheQu] = None
        lvJieJueAverage[valueCellSheQu] = None
        lvWeiJieJueAverage[valueCellSheQu] = None
        lvManYiAverage[valueCellSheQu] = None
        lvJiBenAverage[valueCellSheQu] = None
        lvBuManYiAverage[valueCellSheQu] = None
        lvManYiFinalAverage[valueCellSheQu] = None
        deFenLuoFen[valueCellSheQu] = None
        shiFenLuoFen[valueCellSheQu] = None

logger.debug('承办部门-科室：{}'.format(listKeShi))
logger.debug('承办部门-社区/村：{}'.format(listSheQu))

colChengBanJieDan = get_col_index(sheetJieDan, 2, '承办部门')
colLeiXingJieDan = get_col_index(sheetJieDan, 2, '派单类型')

maxRowJieDan = sheetJieDan.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
lineIndexJieDan = 3
while lineIndexJieDan <= maxRowJieDan:
    valueChengBanJieDan = sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value
    valueLeiXingJieDan = sheetJieDan.cell(lineIndexJieDan, colLeiXingJieDan).value
    if valueChengBanJieDan is not None and valueLeiXingJieDan is not None :
        valueChengBanJieDan = valueChengBanJieDan.strip()
        valueLeiXingJieDan = valueLeiXingJieDan.strip()
        if valueChengBanJieDan in listKeShi or valueChengBanJieDan in listSheQu:
            if valueLeiXingJieDan == '直派':
                listJieDanZhi[valueChengBanJieDan] = listJieDanZhi[valueChengBanJieDan] + 1
                sheetJieDan.delete_rows(lineIndexJieDan)
                maxRowJieDan = maxRowJieDan - 1
            else:
                if valueLeiXingJieDan == '转派':
                    listJieDanZhuan[valueChengBanJieDan] = listJieDanZhuan[valueChengBanJieDan] + 1
                    sheetJieDan.delete_rows(lineIndexJieDan)
                    maxRowJieDan = maxRowJieDan - 1
                else:
                    lineIndexJieDan = lineIndexJieDan + 1
        else:
            lineIndexJieDan = lineIndexJieDan + 1
    else:
        lineIndexJieDan = lineIndexJieDan + 1

logger.debug('接单量-直派：{}'.format(listJieDanZhi))
logger.debug('接单量-转派：{}'.format(listJieDanZhuan))
maxRowJieDan2 = sheetJieDan.max_row
if maxRowJieDan2 > 2:
    # 保存匹配承办部门后的表格
    bookJieDan.save(fileJieDanNo)
    logger.debug('保存  {}'.format(fileJieDanNo))

colChengBanIndex = get_col_index(sheetKaoHe, 2, '承办部门')
colJieDanIndex = get_col_index(sheetKaoHe, 2, '接单部门')
colLeiXing = get_col_index(sheetKaoHe, 2, '派单类型')
colLianXi = get_col_index(sheetKaoHe, 2, '联系')
colJieJue = get_col_index(sheetKaoHe, 2, '解决')
colManYi = get_col_index(sheetKaoHe, 2, '满意')
logger.debug('承办部门：{}列  '.format(colChengBanIndex))
logger.debug('接单部门：{}列'.format(colJieDanIndex))
logger.debug('派单类型：{}列'.format(colLeiXing))
logger.debug('联系：{}列'.format(colLianXi))
logger.debug('解决：{}列'.format(colJieJue))
logger.debug('满意：{}列'.format(colManYi))
lineIndex = 3
while lineIndex <= maxRowQuanBu:
    valueChengBanCur = sheetKaoHe.cell(lineIndex, colChengBanIndex).value
    valueJieDanCur = sheetKaoHe.cell(lineIndex, colJieDanIndex).value
    valueLeiXing = sheetKaoHe.cell(lineIndex, colLeiXing).value
    valueLianxi = sheetKaoHe.cell(lineIndex, colLianXi).value
    valueJieJue = sheetKaoHe.cell(lineIndex, colJieJue).value
    valueManYi = sheetKaoHe.cell(lineIndex, colManYi).value
    if valueChengBanCur is not None and valueJieDanCur is not None and valueLeiXing is not None \
        and valueLianxi is not None and valueJieJue is not None and valueManYi is not None:
        valueChengBanCur = valueChengBanCur.strip()
        valueJieDanCur = valueJieDanCur.strip()
        valueLeiXing = valueLeiXing.strip()
        valueLianxi = valueLianxi.strip()
        valueJieJue = valueJieJue.strip()
        valueManYi = valueManYi.strip()
        if valueChengBanCur in listKeShi or valueChengBanCur in listSheQu:
            if valueLeiXing == '直派':
                listKaoHeZhi[valueChengBanCur] = listKaoHeZhi[valueChengBanCur] + 1
                if valueLianxi == '已联系':
                    listLianXiZhi[valueChengBanCur] = listLianXiZhi[valueChengBanCur] + 1
                if valueLianxi == '未联系':
                    listWeiLianXiZhi[valueChengBanCur] = listWeiLianXiZhi[valueChengBanCur] + 1
                if valueJieJue == '已解决':
                    listJieJueZhi[valueChengBanCur] = listJieJueZhi[valueChengBanCur] + 1
                if valueJieJue == '未解决':
                    listWeiJieJueZhi[valueChengBanCur] = listWeiJieJueZhi[valueChengBanCur] + 1
                if valueManYi == '满意' or valueManYi == '非常满意':
                    listManYiZhi[valueChengBanCur] = listManYiZhi[valueChengBanCur] + 1
                if valueManYi == '基本满意':
                    listJiBenZhi[valueChengBanCur] = listJiBenZhi[valueChengBanCur] + 1
                if valueManYi == '不满意' or valueManYi == '非常不满意':
                    listBuManYiZhi[valueChengBanCur] = listBuManYiZhi[valueChengBanCur] + 1
                sheetKaoHe.delete_rows(lineIndex)
                maxRowQuanBu = maxRowQuanBu - 1
            else:
                if valueLeiXing == '转派':
                    listKaoHeZhuan[valueChengBanCur] = listKaoHeZhuan[valueChengBanCur] + 1
                    if valueLianxi == '已联系':
                        listLianXiZhuan[valueChengBanCur] = listLianXiZhuan[valueChengBanCur] + 1
                    if valueLianxi == '未联系':
                        listWeiLianXiZhuan[valueChengBanCur] = listWeiLianXiZhuan[valueChengBanCur] + 1
                    if valueJieJue == '已解决':
                        listJieJueZhuan[valueChengBanCur] = listJieJueZhuan[valueChengBanCur] + 1
                    if valueJieJue == '未解决':
                        listWeiJieJueZhuan[valueChengBanCur] = listWeiJieJueZhuan[valueChengBanCur] + 1
                    if valueManYi == '满意' or valueManYi == '非常满意':
                        listManYiZhuan[valueChengBanCur] = listManYiZhuan[valueChengBanCur] + 1
                    if valueManYi == '基本满意':
                        listJiBenZhuan[valueChengBanCur] = listJiBenZhuan[valueChengBanCur] + 1
                    if valueManYi == '不满意' or valueManYi == '非常不满意':
                        listBuManYiZhuan[valueChengBanCur] = listBuManYiZhuan[valueChengBanCur] + 1
                    sheetKaoHe.delete_rows(lineIndex)
                    maxRowQuanBu = maxRowQuanBu - 1
                else:
                    lineIndex = lineIndex + 1
        else:
            lineIndex = lineIndex + 1
    else:
        lineIndex = lineIndex + 1

logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))


countKaoHeZhi = 0
countKaoHeZhuan = 0
countLianXiZhi = 0
countLianXiZhuan = 0
countWeiLianXiZhi = 0
countWeiLianXiZhuan = 0
countJieJueZhi = 0
countJieJueZhuan = 0
countWeiJieJueZhi = 0
countWeiJieJueZhuan = 0
countManYiZhi = 0
countManYiZhuan = 0
countJiBenZhi = 0
countJIBenZhuan = 0
countBuManYiZhi = 0
countBuManYiZhuan = 0

for indexTongJi in range(0, len(listKeShi)):
    nameTongJi = listKeShi[indexTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]


for indexTongJi in range(0, len(listSheQu)):
    nameTongJi = listSheQu[indexTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]

logger.debug('考核-转派：{}'.format(countKaoHeZhuan))
logger.debug('联系-转派：{}'.format(countLianXiZhuan))
logger.debug('未联系-转派：{}'.format(countWeiLianXiZhuan))
logger.debug('解决-转派：{}'.format(countJieJueZhuan))
logger.debug('未解决-转派：{}'.format(countWeiJieJueZhuan))
logger.debug('满意-转派：{}'.format(countManYiZhuan))
logger.debug('基本满意-转派：{}'.format(countJIBenZhuan))
logger.debug('不满意-转派：{}'.format(countBuManYiZhuan))

logger.debug('考核-直派：{}'.format(countKaoHeZhi))
logger.debug('联系-直派：{}'.format(countLianXiZhi))
logger.debug('未联系-直派：{}'.format(countWeiLianXiZhi))
logger.debug('解决-直派：{}'.format(countJieJueZhi))
logger.debug('未解决-直派：{}'.format(countWeiJieJueZhi))
logger.debug('满意-直派：{}'.format(countManYiZhi))
logger.debug('基本满意-直派：{}'.format(countJiBenZhi))
logger.debug('不满意-直派：{}'.format(countBuManYiZhi))


lvLianxiCount = round((round(countLianXiZhuan/countKaoHeZhuan*100) + round(countLianXiZhi/countKaoHeZhi*100))/2)
lvJieJueCount = round((round(countJieJueZhuan/countKaoHeZhuan*100) + round(countJieJueZhi/countKaoHeZhi*100))/2)
lvManYiCount = round((round(countManYiZhuan/countKaoHeZhuan*100) + round(countManYiZhi/countKaoHeZhi*100))/2)
lvJiBenCount = round((round(countJIBenZhuan/countKaoHeZhuan*100) + round(countJiBenZhi/countKaoHeZhi*100))/2)
lvManYiFinalCount = round((lvManYiCount + lvJiBenCount*0.8))
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))

lvLianxiCount = round((countLianXiZhuan/countKaoHeZhuan*100 + countLianXiZhi/countKaoHeZhi*100)/2)
lvJieJueCount = round((countJieJueZhuan/countKaoHeZhuan*100 + countJieJueZhi/countKaoHeZhi*100)/2)
lvManYiCount = round((countManYiZhuan/countKaoHeZhuan*100 + countManYiZhi/countKaoHeZhi*100)/2)
lvJiBenCount = round((countJIBenZhuan/countKaoHeZhuan*100 + countJiBenZhi/countKaoHeZhi*100)/2)
lvManYiFinalCount = round((lvManYiCount + lvJiBenCount*0.8))
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))

luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))


def make_pai_ming_shu_ju(list_src):
    for rowIndex in range(0, len(list_src)):
        buMenName = list_src[rowIndex]
        if listKaoHeZhi[buMenName] != 0:
            lvLianXiZhi[buMenName] = round(listLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100)
            lvWeiLianXiZhi[buMenName] = round(listWeiLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100)
            lvJieJueZhi[buMenName] = round(listJieJueZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvWeiJieJueZhi[buMenName] = round(listWeiJieJueZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvManYiZhi[buMenName] = round(listManYiZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvJiBenZhi[buMenName] = round(listJiBenZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvBuManYiZhi[buMenName] = round(listBuManYiZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvManYiFinalZhi[buMenName] = round(lvManYiZhi[buMenName] + lvJiBenZhi[buMenName]*0.8)
        if listKaoHeZhuan[buMenName] != 0:
            lvLianXiZhuan[buMenName] = round(listLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvWeiLianXiZhuan[buMenName] = round(listWeiLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvJieJueZhuan[buMenName] = round(listJieJueZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvWeiJieJueZhuan[buMenName] = round(listWeiJieJueZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvManYiZhuan[buMenName] = round(listManYiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvJiBenZhuan[buMenName] = round(listJiBenZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvBuManYiZhuan[buMenName] = round(listBuManYiZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvManYiFinalZhuan[buMenName] = round(lvManYiZhuan[buMenName] + lvJiBenZhuan[buMenName] * 0.8)
        # 联系率计算平均值
        deFenLianXi = round((listLianXiZhi[buMenName]/countKaoHeZhi*100
                             + listLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, 3)
        shiFenLianXi = round((listWeiLianXiZhi[buMenName]/countKaoHeZhi*100
                             + listWeiLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, 3)
        deFenJieJue = round((listJieJueZhi[buMenName]/countKaoHeZhi*100
                             + listJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, 3)
        shiFenJieJue = round((listWeiJieJueZhi[buMenName]/countKaoHeZhi*100
                             + listWeiJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, 3)
        deFenShangMan = round((listManYiZhi[buMenName]/countKaoHeZhi*100
                               + listManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, 5)
        deFenJiBen = round((listJiBenZhi[buMenName]/countKaoHeZhi*100
                            + listJiBenZhuan[buMenName]/countKaoHeZhuan*100)/2, 5)
        ShiFenBuManYi = round((listBuManYiZhi[buMenName]/countKaoHeZhi*100
                               + listBuManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, 3)
        deFenManYi = round(deFenShangMan + deFenJiBen*0.8, 3)
        shiFenManYi = round(ShiFenBuManYi + deFenJiBen*0.2, 3)

        deFenLuoFen[buMenName] = round(deFenLianXi*0.1+deFenJieJue*0.5+deFenManYi*0.4, 3)
        shiFenLuoFen[buMenName] = round(shiFenLianXi*0.1+shiFenJieJue*0.5+shiFenManYi*0.4, 3)

        if lvLianXiZhi[buMenName] is not None and lvLianXiZhuan[buMenName] is not None:
            lvLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvLianXiZhuan[buMenName]) / 2)
        else:
            if lvLianXiZhi[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhi[buMenName]
            if lvLianXiZhuan[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhuan[buMenName]

        # 未联系率计算平均值
        if lvWeiLianXiZhi[buMenName] is not None and lvWeiLianXiZhuan[buMenName] is not None:
            lvWeiLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvWeiLianXiZhuan[buMenName]) / 2)
        else:
            if lvWeiLianXiZhi[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhi[buMenName]
            if lvWeiLianXiZhuan[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhuan[buMenName]

        # 解决率计算平均值
        if lvJieJueZhi[buMenName] is not None and lvJieJueZhuan[buMenName] is not None:
            lvJieJueAverage[buMenName] = round((lvJieJueZhi[buMenName] + lvJieJueZhuan[buMenName]) / 2)
        else:
            if lvJieJueZhi[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhi[buMenName]
            if lvJieJueZhuan[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhuan[buMenName]

        # 未解决率计算平均值
        if lvWeiJieJueZhi[buMenName] is not None and lvWeiJieJueZhuan[buMenName] is not None:
            lvWeiJieJueAverage[buMenName] = round((lvWeiJieJueZhi[buMenName] + lvWeiJieJueZhuan[buMenName]) / 2)
        else:
            if lvWeiJieJueZhi[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhi[buMenName]
            if lvWeiJieJueZhuan[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhuan[buMenName]

        # 满意率计算平均值
        if lvManYiZhi[buMenName] is not None and lvManYiZhuan[buMenName] is not None:
            lvManYiAverage[buMenName] = round((lvManYiZhi[buMenName] + lvManYiZhuan[buMenName]) / 2)
        else:
            if lvManYiZhi[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhi[buMenName]
            if lvManYiZhuan[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhuan[buMenName]

        # 基本满意率计算平均值
        if lvJiBenZhi[buMenName] is not None and lvJiBenZhuan[buMenName] is not None:
            lvJiBenAverage[buMenName] = round((lvJiBenZhi[buMenName] + lvJiBenZhuan[buMenName]) / 2)
        else:
            if lvJiBenZhi[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhi[buMenName]
            if lvJiBenZhuan[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhuan[buMenName]

        # 不满意率计算平均值
        if lvBuManYiZhi[buMenName] is not None and lvBuManYiZhuan[buMenName] is not None:
            lvBuManYiAverage[buMenName] = round((lvBuManYiZhi[buMenName] + lvBuManYiZhuan[buMenName]) / 2)
        else:
            if lvBuManYiZhi[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhi[buMenName]
            if lvBuManYiZhuan[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhuan[buMenName]

        # 最终满意率计算平均值
        if lvManYiFinalZhi[buMenName] is not None and lvManYiFinalZhuan[buMenName] is not None:
            lvManYiFinalAverage[buMenName] = round((lvManYiFinalZhi[buMenName] + lvManYiFinalZhuan[buMenName]) / 2)
        else:
            if lvManYiFinalZhi[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhi[buMenName]
            if lvManYiFinalZhuan[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhuan[buMenName]

        logger.debug('{} 直派 考核：{}  联系：{} 联系率：{} 未联系：{} 未联系率：{} 解决：{} 解决率：{} 未解决：{} 未解决率：{} '
                     '满意：{} 满意率：{} 基本满意：{} 基本满意率：{} 不满意：{} 不满意率：{}'
                     .format(buMenName, listKaoHeZhi[buMenName], listLianXiZhi[buMenName], lvLianXiZhi[buMenName]
                             , listWeiLianXiZhi[buMenName], lvWeiLianXiZhi[buMenName], listJieJueZhi[buMenName]
                             , lvJieJueZhi[buMenName], listWeiJieJueZhi[buMenName], lvWeiJieJueZhi[buMenName]
                             , listManYiZhi[buMenName], lvManYiZhi[buMenName], listJiBenZhi[buMenName]
                             , lvJiBenZhi[buMenName], listBuManYiZhi[buMenName], lvBuManYiZhi[buMenName]))
        logger.debug('{} 转派 考核：{}  联系：{} 联系率：{} 未联系：{} 未联系率：{} 解决：{} 解决率：{} 未解决：{} 未解决率：{} '
                     '满意：{} 满意率：{} 基本满意：{} 基本满意率：{} 不满意：{} 不满意率：{}'
                     .format(buMenName, listKaoHeZhuan[buMenName], listLianXiZhuan[buMenName], lvLianXiZhuan[buMenName]
                             , listWeiLianXiZhuan[buMenName], lvWeiLianXiZhuan[buMenName], listJieJueZhuan[buMenName]
                             , lvJieJueZhuan[buMenName], listWeiJieJueZhuan[buMenName], lvWeiJieJueZhuan[buMenName]
                             , listManYiZhuan[buMenName], lvManYiZhuan[buMenName], listJiBenZhuan[buMenName]
                             , lvJiBenZhuan[buMenName], listBuManYiZhuan[buMenName], lvBuManYiZhuan[buMenName]))
        logger.debug('{} 平均  联系率：{} 未联系率：{} 解决率：{} 未解决率：{} 满意率：{} 基本满意率：{} 不满意率：{}'
                     .format(buMenName, lvLianXiAverage[buMenName], lvWeiLianXiAverage[buMenName]
                             , lvJieJueAverage[buMenName], lvWeiJieJueAverage[buMenName], lvManYiAverage[buMenName]
                             , lvJiBenAverage[buMenName], lvBuManYiAverage[buMenName]))


make_pai_ming_shu_ju(listKeShi)
make_pai_ming_shu_ju(listSheQu)


def insert_into_sheet(sheet_insert, inset_bu_men, de_fen):
    rowCount = sheet_insert.max_row
    rowInsert = rowCount + 1
    colDeFen = get_col_index(sheet_insert, 2, '得分')
    if de_fen is not None:
        for rowPai in range(4, rowCount+1):
            deFenRow = sheet_insert.cell(rowPai, colDeFen).value
            if deFenRow is not None:
                if de_fen >= deFenRow:
                    sheet_insert.insert_rows(rowPai)
                    rowInsert = rowPai
                    break
            else:
                sheet_insert.insert_rows(rowPai)
                rowInsert = rowPai
                break
    # logger.debug('{} 得分：{}  插入到{}行 共{}行 '.format(inset_bu_men, de_fen, rowInsert, rowCount))
    sheet_insert.cell(rowInsert, 2).value = inset_bu_men            # 接单部门
    sheet_insert.cell(rowInsert, 3).value = listJieJueZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    sheet_insert.cell(rowInsert, 4).value = listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    sheet_insert.cell(rowInsert, 5).value = listJieJueZhi[inset_bu_men]  # 接单总量(件)-直派
    sheet_insert.cell(rowInsert, 6).value = listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men]  # 纳入考核(件)-总量
    sheet_insert.cell(rowInsert, 7).value = listKaoHeZhuan[inset_bu_men]  # 纳入考核(件)-转派
    sheet_insert.cell(rowInsert, 8).value = listKaoHeZhi[inset_bu_men]  # 纳入考核(件)
    sheet_insert.cell(rowInsert, 9).value = listLianXiZhuan[inset_bu_men]  # 联系(件)-转派
    sheet_insert.cell(rowInsert, 10).value = listLianXiZhi[inset_bu_men]  # 联系(件)-直派
    sheet_insert.cell(rowInsert, 11).value = listWeiLianXiZhuan[inset_bu_men]  # 未联系(件)-转派
    sheet_insert.cell(rowInsert, 12).value = listWeiLianXiZhi[inset_bu_men]  # 未联系(件)-直派
    sheet_insert.cell(rowInsert, 13).value = listJieJueZhuan[inset_bu_men]  # 解决(件)-转派
    sheet_insert.cell(rowInsert, 14).value = listJieJueZhi[inset_bu_men]  # 解决(件)-直派
    sheet_insert.cell(rowInsert, 15).value = listWeiJieJueZhuan[inset_bu_men]  # 未解决(件)-转派
    sheet_insert.cell(rowInsert, 16).value = listWeiJieJueZhi[inset_bu_men]  # 未解决(件)-直派
    sheet_insert.cell(rowInsert, 17).value = listManYiZhuan[inset_bu_men]  # 满意(件)-转派
    sheet_insert.cell(rowInsert, 18).value = listManYiZhi[inset_bu_men]  # 满意(件)-直派
    sheet_insert.cell(rowInsert, 19).value = listJiBenZhuan[inset_bu_men]  # 基本满意(件)-转派
    sheet_insert.cell(rowInsert, 20).value = listJiBenZhi[inset_bu_men]  # 基本满意(件)-直派
    sheet_insert.cell(rowInsert, 21).value = listBuManYiZhuan[inset_bu_men]  # 不满意(件)-转派
    sheet_insert.cell(rowInsert, 22).value = listBuManYiZhi[inset_bu_men]  # 不满意(件)-直派
    sheet_insert.cell(rowInsert, 23).value = lvLianXiZhuan[inset_bu_men]  # 响应率(%)-转派
    sheet_insert.cell(rowInsert, 24).value = lvLianXiZhi[inset_bu_men]  # 响应率(%)-直派
    sheet_insert.cell(rowInsert, 25).value = lvLianXiAverage[inset_bu_men]  # 响应率(%)-平均
    sheet_insert.cell(rowInsert, 26).value = lvJieJueZhuan[inset_bu_men]  # 解决率(%)-转派
    sheet_insert.cell(rowInsert, 27).value = lvJieJueZhi[inset_bu_men]  # 解决率(%)-直派
    sheet_insert.cell(rowInsert, 28).value = lvJieJueAverage[inset_bu_men]  # 解决率(%)-平均
    sheet_insert.cell(rowInsert, 29).value = lvManYiFinalZhuan[inset_bu_men]  # 满意率(%)-转派
    sheet_insert.cell(rowInsert, 30).value = lvManYiFinalZhi[inset_bu_men]  # 满意率(%)-直派
    sheet_insert.cell(rowInsert, 31).value = lvManYiFinalAverage[inset_bu_men]  # 满意率(%)-平均
    sheet_insert.cell(rowInsert, 32).value = de_fen  # 得分
    sheet_insert.cell(rowInsert, 33).value = deFenLuoFen[inset_bu_men]  # 裸分得分
    sheet_insert.cell(rowInsert, 34).value = shiFenLuoFen[inset_bu_men]  # 裸分失分


def make_pai_ping(sheet_make, list_make):
    for indexName in range(0, len(list_make)):
        nameIndex = list_make[indexName]
        numDeFen = None
        lvManYiAll = None
        if listKaoHeZhi[nameIndex] + listKaoHeZhuan[nameIndex] > 0:
            if lvManYiAverage[nameIndex] is not None and lvJiBenAverage[nameIndex] is not None:
                lvManYiAll = lvManYiAverage[nameIndex]+lvJiBenAverage[nameIndex]*0.8
            else:
                if lvManYiAverage[nameIndex] is not None:
                    lvManYiAll = lvManYiAverage[nameIndex]
                if lvJiBenAverage[nameIndex] is not None:
                    lvManYiAll = lvJiBenAverage[nameIndex]*0.8
            numDeFen = round(lvLianXiAverage[nameIndex] * 0.1 + lvJieJueAverage[nameIndex] * 0.5 + lvManYiAll * 0.4)
        # logger.debug('{} 得分：{} '.format(nameIndex, numDeFen))
        insert_into_sheet(sheet_make, nameIndex, numDeFen)


make_pai_ping(sheetKeShi, listKeShi)
make_pai_ping(sheetSheQu, listSheQu)

# def inset_into_sheet_in_line(line)
bookKaoHe.save(fileResultSave)


